from openpyxl import load_workbook
from tqdm import tqdm
import  csv
import json
import requests
sourceFile="SponsoredBranding_Keywords.xlsx"
wb=load_workbook(sourceFile)
sheet=wb['Sheet1']
sourceRows=sheet.max_row
sourceData=[]
for index in (range(sourceRows)):
    sourceData.append(sheet.cell(index+1,1).value)

sourceData=sourceData[1:]

#二维数组，为了能多行写入csv
toCsvList=[]

for index_source,source in enumerate(tqdm(sourceData)):
    oneColData=[]


    url = "http://172.16.35.43:8765/sponsored/getSponsoredBrandingByKeyword"
    params={'CountryCode':'USA','CompanyCode':'1003','blockedSeller':'true','page':'1','rows':'1','app':'WWW','keyword':source}
    headers = {'Accept': 'application/json', 'Content-Type': 'application/json'}

    r= requests.get(url=url,params=params,headers=headers)
    resultMsg=r.json().get("resultMsg")
    data=r.json().get("data")

    if resultMsg=='query sucess!' and data!=None:
        oneColData.append(source)
    if oneColData!=[]:
        toCsvList.append(oneColData)

with open(sourceFile[:-4]+'csv','w',newline='')as file:
    op=csv.writer(file)
    op.writerows(toCsvList)



